<?php
/**
 * 关于书籍的模型（对于数据库的操作）
 */
class Book_model extends Model{

   /**
    * 模型构造函数
    */
    function Book_model(){
        parent::Model();
        $this->load->database();  //加载数据库
    }

    /**
     * 搜索框的数据库执行函数，使用关键字搜索图书。
     * 会先对关键字进行清理去除多余空格；按空格分词，识别ISBN，全英文等特殊情况，自动寻找最高效搜索方式
     */
    function bookSearchResults($input,$total,$offset,$perpage)
	{
       if (preg_match("/[\x7f-\xff]/", $input)) { //兼容gb2312,utf-8
          $has_chinese=1;     //含有中文，不论中文是在开头，结尾还是中间
          $searchwords=split(" ", $input); //含有中文，需要按空格分词
        } else {   //如果不含中文，不要按空格分词，避免OR查询recall高，precision低。
           $has_chinese=0;
           $searchwords[0]=$input;
       }

       if($total==1)
         $sql = "SELECT count(id) as number FROM book where ";
       else
  	     $sql = "SELECT * FROM book where ";

       for($i=0;$i<count($searchwords);$i++){
            $searchword=$searchwords[$i];
            if($searchword==NULL)  //there is one or several space after the query.
             continue;
            $is_isbn=0;
            if($searchword{0}>='0'&&$searchword{0}<='9'&& strlen($searchword)>=10){  //判断为isbn，按-号重新组合
               $is_isbn=1;
			   $digits=split("-",$searchword);
               $newword="";
               foreach($digits as $digit){
                 $newword.=$digit;
               }
               $searchword=$newword;
            }
            if(!is_numeric($searchword))
               $is_isbn=0;
            if(strlen($searchword)>=10&&$searchword{9}=="X")  //780080609X 修正情况
               $is_isbn=1;
            if(strlen($searchword)>=13&&$searchword{12}=="X") //9780080609X 修正情况
               $is_isbn=1;
            //开始进行搜索语句组合
            if($is_isbn==1){  //是isbn
               if($i==0)
               $sql .= " (
                    isbn10  LIKE '$searchword%'
                    OR isbn13 LIKE '$searchword%'
                    ) ";
               else
		       $sql .= " AND (
                    isbn10  LIKE '$searchword%'
                    OR isbn13 LIKE '$searchword%' 
                    ) ";
               }
             else{  //不是isbn
                  if($i==0)
                    $sql .= "( title  LIKE '$searchword%'
                    OR en_title LIKE '$searchword%'
                    OR author LIKE '$searchword%'
                    ) ";
                  else
                    $sql .= "OR ( title  LIKE '$searchword%'
                    OR en_title LIKE '$searchword%'
                    OR author LIKE '$searchword%'
                    ) ";
               }
          }
         if($total==0){
            $sql_order = " ORDER BY num_sales+num_wanted+numdeals DESC,total_num_ratings DESC";
            $sql .= $sql_order;
            $sql.=" LIMIT $offset,$perpage";
         }
	    $query = $this->db->query($sql);
        if ($query->num_rows() > 0){
           $bookarray=$query->result() ;
           return $bookarray;
          }
        else
           return null;
	}

    /**
     * @param <type> $total
     * @param <type> $offset
     * @param <type> $per_page
     * @param <type> $title
     * @param <type> $en_title
     * @param <type> $author
     * @param <type> $publisher
     * @param <type> $edition
     * @param <type> $language
     * @return <type>
     * 按照各输入参数，以与逻辑进行高级搜索
     */
    function advancedSearch($total,$offset,$per_page,$title,$en_title,$author,$publisher,$edition,$language){
        if($total==1)
          $sql = "SELECT count(id) as number FROM book where ";
        else
         $sql = "SELECT * FROM book WHERE ";
         $value1=0;
         if($title!=null){
            $sql.="title LIKE '$title%' ";
            $value1=1;
         }
         if($en_title!=null){
            if($value1==1)
            $sql.=" AND en_title LIKE '$en_title%' ";
            else{
                $sql.=" en_title LIKE '$en_title%' ";
                $value1=1;
            }
         }
         if($author!=null){
            if($value1==1)
            $sql.=" AND author LIKE '$author%' ";
            else{
                $sql.=" author LIKE '$author%' ";
                $value1=1;
            }
         }
         if($publisher!=null){
            if($value1==1)
            $sql.=" AND publisher LIKE '$publisher%' ";
            else{
                $sql.=" publisher LIKE '$publisher%' ";
                $value1=1;
            }
         }
        if($edition!=null){
            if($value1==1)
              $sql.=" AND edition='$edition' ";
            else{
                $sql.=" edition='$edition' ";
                $value1=1;
            }
         }
         if($language!=null){
            if($value1==1)
              $sql.=" AND language='$language' ";
            else{ 
                $sql.=" language='$language' ";
                $value1=1;
            }
         }
        if($total==0){
           $sql.=" ORDER BY total_num_ratings DESC ";
           $sql.=" LIMIT $offset,$per_page ";
        }
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0){
           $bookarray=$query->result() ;
           return $bookarray;
          }
        else
           return null;
    }

    /**
     * 搜索建议的数据库执行函数
     * @param <type> $query
     * @return <type>
     */
    function getQuerySuggestion($query)
    {
        // Parse the keywords...
        $query=trim($query);
        if($query==NULL)
        {
            return null;
        }
        $keywords=split(" ", $query);
        $bookSQL="SELECT * FROM book where ";
        $is_isbn=0;
        $is_isbn13=0;
        for($i=0;$i<count($keywords);$i++)
        {
            $keyword=$keywords[$i];
            // Ignore whitespaces...
            if($keyword==NULL)
            {
                continue;
            }
            // Parse ISBN...
            if($keyword{0}>='0' && $keyword{0}<='9')
            {
                if($keyword{0}==9)
                   $is_isbn13=1;
                else
                   $is_isbn=1;
            }

            if(!is_numeric($keyword)){
                $is_isbn=0;
                $is_isbn13=0;
            }

            // Add an OR statement to process the second keyword...
            if($i>0)
            {
                $bookSQL.="OR";
            }
            $bookSQL.=" ( title LIKE '$keyword%' OR en_title LIKE '$keyword%'  OR isbn10 LIKE '$keyword%' OR isbn13 LIKE '$keyword%' ) ";
        }

        // Determine the order for the books query...
        $bookSQL.=" ORDER BY total_num_ratings DESC LIMIT 0,10";
        $bookQuery=$this->db->query($bookSQL);

        $result=array();
        // The maximum number of suggestions...
        $MAX_RESULT=10;
        $count=0;
        // Now we choose the suggestion...
        foreach($bookQuery->result() as $bookInfo)
        {
		       if($is_isbn==1)
		       {
                    $suggestion=$bookInfo->isbn10;
                }
               else if ($is_isbn13==1){
                   $suggestion=$bookInfo->isbn13;
               }
		       else
		       {
                    $suggestion=$bookInfo->title;
                }
                $result[]=$suggestion;
            }
        return $result;
    }

    /**
     * 获取书籍详情
     */
    function get_book_detail($bookid){
        $book_query_string="SELECT * FROM book WHERE id={$bookid}";
        $book_query = $this->db->query($book_query_string);
        if( ($book_query->row_array())==null ){
            return null;
        }
        else{
            $book_result=$book_query->result();
            return $book_result[0]; //直接获取图书对象
        }
    }


     /**
      * 获取最热门书籍，按照诸多因素排序
      */
    function get_hottest_book($total,$offset,$perpage){
        if($total==1)
          $query_string="SELECT count(id) as number FROM book";
        else
         $query_string="SELECT * FROM book ORDER BY num_sales+num_wanted+numdeals DESC,total_num_ratings DESC LIMIT $offset , $perpage ";
        $query = $this->db->query($query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

    /**
     * 暂时未调用：删除书籍#######
     */
    function delete_book($isbn){
        $delete_string="DELETE FROM books WHERE isbn={$isbn}";
        $this->db->query($delete_string);
        if(($this->db->affected_rows())!=1){
            return 0;      //如果删除操作影响的行数不为1，则表示删除失败，返回0
        }
        else{
            return 1;    //如果删除成功，返回1
        }
    }

    /**
     * 用10位ISBN判断书籍是否已经存在
     */
    function if_isbn10_exist($get_isbn){
        if($get_isbn==null)
           return 0;
        $this->db->where('isbn10', $get_isbn);
        $query=$this->db->get('book');
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有书籍的记录，则返回0
        }
        else{
            return 1;   //如果数据库中有符合ISBN的记录则返回1
        }
    }

    /**
     * 用13位ISBN判断书籍是否已经存在
     */
    function if_isbn13_exist($get_isbn){
        if($get_isbn==null)
           return 0;
        $this->db->where('isbn13', $get_isbn);
        $query=$this->db->get('book');
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有书籍的记录，则返回0
        }
        else{
            return 1;   //如果数据库中有符合ISBN的记录则返回1
        }
    }


    /*
     * 向数据库插入新书籍条目
     */
    function insert_book($book_data){
        $data = array(
            'title' => $book_data['title'] ,
            'isbn10' => $book_data['isbn10'] ,
            'isbn13' => $book_data['isbn13'] ,
            'addby_userid'=>$book_data['addby_userid'],
            'author' => $book_data['author'] ,
            'en_title'=>$book_data['en_title'],
            'category_id'=>$book_data['category_1'],
            'category_2_id'=>$book_data['category_2'],
            'publisher' => $book_data['publisher'] ,
            'publish_year' => $book_data['year'] ,
            'publish_month' => $book_data['month'] ,
            'pages' => $book_data['pages'] ,
            'edition' => $book_data['edition'] ,
            'price' => $book_data['price'] ,
            'pic' => $book_data['pic'] ,
            'language'=>$book_data['language']
        );
        $this->db->insert('book', $data);    //插入数据
        if(($this->db->affected_rows())!=1){
            return 0;      //如果插入操作影响的行数不为1，则表示插入失败，返回0
        }
        else{
            $id=$book_data['addby_userid'];
            $sql="UPDATE user SET money=money+100 WHERE id='$id' ";
            $query=$this->db->query($sql);
            return 1;    //如果插入成功，返回1
        }
    }



    /**
     *根据书籍种类在搜索结果中展示书籍
     */
    function bookByCategory($total,$catelevel,$cateid,$offset,$per_page){
        if($total==1)
          $query_string="SELECT count(id) as number FROM book";
        else
          $query_string="SELECT * FROM book";
        if($catelevel==1)
           $query_string.=" WHERE category_id=$cateid OR 2nd_category_id=$cateid ORDER BY total_num_ratings DESC ";
        else
           $query_string.=" WHERE category_2_id=$cateid OR 2nd_category_2_id=$cateid ORDER BY total_num_ratings DESC ";
        if($total==0)
           $query_string.="LIMIT $offset,$per_page";
        $query = $this->db->query($query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

	//获取书易仓库书籍的详情
    function get_shuyibook_detail($isbn13){
        $book_query_string="SELECT * FROM warehouse_detail WHERE isbn13='{$isbn13}'";
        $book_query = $this->db->query($book_query_string);
        if( ($book_query->row_array())==null ){
            return null;
        }
        else{
            $book_result=$book_query->result();
            return $book_result; //获取所有相同isbn的图书对象
        }
    }

    //获取浙大热门书单中书籍情况
    function get_booklist(){
        $book_query_string="SELECT * FROM booklist ";
        $book_query = $this->db->query($book_query_string);
        if( ($book_query->row_array())==null ){
            return null;
        }
        else{
            $book_result=$book_query->result();
            return $book_result; //获取所有相同isbn的图书对象
        }
    }
 

}

?>